import cherrypy
import sqlite3
import time

import Common

class Subforum(Common.Template):
	# Call the default function with no arguments
	def index(self):
		return self.default()
	index.exposed = True

	# The default view displays a given subforum.  If no ID is given, it 
	# displays the root subforum
	def default(self, id = None):		
		# If no ID is given, we are viewing the root.  Set id to NULL for queries
		if id == None or id == "None":
			id = "NULL"
		
		# Get the subforum name
		subforum_name = self.get_subforum_name(id)
		
		if subforum_name == None and id != "NULL":
			# The subforum doesn't exist so make an error page			
			yield self.error("The specified subforum does not exist!", True)			
		else:
			# Print the top of the page
			yield self.header(subforum_name)
			yield self.links()			
			yield self.breadcrumbs(SubforumID = id)			
			
			# Print the subforum list
			yield self.generator_expand(self.subforum_list(id))	
			
			# Print the thread list
			yield self.generator_expand(self.thread_list(id))
			
			yield self.footer()		
	default.exposed = True
	
	# This function prints the subforums contained by the parent subforum
	def subforum_list(self, id):	
		cursor = cherrypy.thread_data.db.cursor() 	
		
		if self.is_parent(id):	
			yield "<br /><table class='subforum_list' width='100%'>"
			yield "<tr><th colspan='3' class='subforum_list'>Subforums</th></tr>"
			# This is a "parent" subforum, which means there are subforum beneath it, so list these			
			cursor.execute("SELECT * FROM Subforum WHERE ParentSubforumID IS ? ORDER BY RelativeOrder", (id,))
			for ID, Name, Description, ParentSubforumID, ThreadCount, PostCount, RelativeOrder in cursor.fetchall():
				# compile list of moderators for this subforum
				modlist = self.getModeratorsOf(ID)
				mods = ""
				if len(modlist) > 0:
					for uid, mod in modlist:
						mods += '<a href="/User/%s">%s</a>, ' % (uid, mod)
					mods = mods.rstrip(', ')
				else:
					mods = "<i>None</i>"
				# output
				yield "<tr>"
				yield "<td class='subforum_list'><h2 class='subforum_list'><a href='/Subforum/%s'>%s</a></h2>%s<br />Moderators: %s</td>" % (ID, Name, Description, mods)
				yield "<td class='subforum_list' width='100px'>Threads: %s<br />Posts: %s</td>" % (ThreadCount, PostCount)
				yield "</tr>" 
			yield "</table>"
	
	# Returns if a given subforum is a "parent".  This is if the subforum has
	# subforums beneath it.  
	def is_parent(self, id):
		cursor = cherrypy.thread_data.db.cursor() 
		
		# Find children of this subforum
		cursor.execute("SELECT * FROM Subforum WHERE ParentSubforumID IS ?", (id,))
		
		for row in cursor:
			# There is at least one child, so this is a parent
			return True
		
		return False
	
	# This function prints the threads contained by the parent
	def thread_list(self, id):		
		cursor = cherrypy.thread_data.db.cursor() 
		
		yield "<p align='right'>&nbsp;"
		# If a user is logged in, show the new thread button
		if self.getLoginPermission() <= 2:
			yield "<a href='/Thread/New/%s'>New Thread</a>" % id
		yield "&nbsp;</p>"
					
		# Make a table across the screen
		yield "<table class='thread_list' width='100%'>"
		yield "<tr><th colspan='3' class='thread_list'>Threads</th></tr>"
		
		# Get the threads in the current subforum.  Sort them by decreasing ID of their latest post (chronological)
		cursor.execute('''
			SELECT 
				Thread.ID, Post.Title, User.ID, User.Username, Post.Timestamp, Thread.PostCount, Thread.ViewCount, Thread.Sticky, Thread.Closed, Post2.ID, Post2.Title, Post2.Content, User2.ID, User2.Username, Post2.Timestamp
			FROM 
				Post, Thread, User, Post Post2, User User2, 
				(SELECT ThreadID, MIN(Post.ID) AS MinPostID, MAX(Post.ID) AS MaxPostID
				 FROM Post
				 GROUP BY ThreadID) Times
			WHERE
				Thread.ID = Times.ThreadID AND 
				Post.ID = Times.MinPostID AND
				Post.UserID = User.ID AND
				Thread.SubforumID IS ? AND
				Post2.ID = Times.MaxPostID AND
				Post2.UserID = User2.ID
			ORDER BY 
				Thread.Sticky DESC, Times.MaxPostID DESC
			''', (id,))
		
		# Print the thread
		count = 0
		for ID, Title, UserID, Username, Timestamp, PostCount, ViewCount, Sticky, Closed, PostID, PostTitle, PostContent, PostUserID, PostUsername, PostTimestamp in cursor.fetchall():				
			if Sticky == 1:
				Title += " - STICKY"
			if Closed == 1:
				Title += " - CLOSED"
				
			yield "<tr>"
			yield "<td class='thread_list'><h2 class='thread_list'><a href='/Thread/%s'>%s</a></h2>Started by <a href='/User/%s'>%s</a> on %s</td>" % (ID, Title, UserID, Username, time.strftime("%c", time.localtime(Timestamp)))
			yield "<td class='thread_list' width='100px'>Replies: %s<br />Views: %s<br /></td>" % (PostCount, ViewCount)
			if PostTitle == None or PostTitle == "":
				blurb = PostContent
			else:
				blurb = PostTitle
			if len(blurb) > 18:
				blurb = "%s%s" % (blurb[0:18], "...")
			yield "<td class='thread_list' width='200px'><a href='/Thread/%s#%s'>%s</a><br /><a href='/User/%s'>%s</a><br />%s</td>" % (ID, PostID, blurb, PostUserID, PostUsername, time.strftime("%c", time.localtime(PostTimestamp)))
			count += 1
		
		if count == 0:
			yield "<tr><td class='thread_list' colspan='3'><i>There are no threads here</i></td></tr>"
						
		yield "</table>"
		
